﻿using KKU.RBSFPP.Shared.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KKU.RBSFPP.Shared.ReportQuery
{
    public static class ReportQuery
    {
        public static double GetFactorValues(int factorId, int year, int month)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var qry = from it in db.FactorValues.ToList()
                          where it.CreateOn.Year == year
                          && it.DeleteOn.HasValue == false
                          && it.FactorId == factorId
                          group it by it.CreateOn.Date.ToString("MM") into grouping
                          select new
                          {
                              Month = int.Parse(grouping.Key),
                              Value = grouping.Sum(it => it.Value) / grouping.Count(),
                          };

                var data = qry.FirstOrDefault(it => it.Month == month);
                return data == null ? 0 : data.Value;
            }
        }

        public static double GetProductPricing(int categoryId, int year, int month)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var qry = from tracking in db.ProductPricingPerUnitTrackings.ToList()
                          where tracking.Product.CategoryId == categoryId
                          && tracking.CreateOn.Year == year
                          && tracking.DeleteOn.HasValue == false
                          && tracking.Product.DeleteOn.HasValue == false
                          group tracking by tracking.CreateOn.Date.ToString("MM") into grouping
                          select new
                          {
                              Month = int.Parse(grouping.Key),
                              Value = grouping.Sum(it => it.SellingPrice) / grouping.Count(),
                          };

                var data = qry.FirstOrDefault(it => it.Month == month);
                return data == null ? 0 : data.Value;
            }
        }



        // กราฟ
        public static IEnumerable<DisplayForGraph> GetGraphMonthly(int year)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = new List<DisplayForGraph>
                {
                    new DisplayForGraph{Month = 01, },
                    new DisplayForGraph{Month = 02, },
                    new DisplayForGraph{Month = 03, },
                    new DisplayForGraph{Month = 04, },
                    new DisplayForGraph{Month = 05, },
                    new DisplayForGraph{Month = 06, },
                    new DisplayForGraph{Month = 07, },
                    new DisplayForGraph{Month = 08, },
                    new DisplayForGraph{Month = 09, },
                    new DisplayForGraph{Month = 10, },
                    new DisplayForGraph{Month = 11, },
                    new DisplayForGraph{Month = 12, },
                };

                #region คำนวณปัจจัย

                var bathQry = from it in db.FactorValues.ToList()
                              where it.CreateOn.Year == year
                              && it.DeleteOn.HasValue == false
                              && it.FactorId == 1
                              group it by it.CreateOn.Date.ToString("MM") into grouping
                              select new
                              {
                                  Key = int.Parse(grouping.Key),
                                  Value = grouping.Sum(it => it.Value) / grouping.Count(),
                              };
                var rateQry = from it in db.FactorValues.ToList()
                              where it.CreateOn.Year == year
                              && it.DeleteOn.HasValue == false
                              && it.FactorId == 2
                              group it by it.CreateOn.Date.ToString("MM") into grouping
                              select new
                              {
                                  Key = int.Parse(grouping.Key),
                                  Value = grouping.Sum(it => it.Value) / grouping.Count(),
                              };
                var goldQry = from it in db.FactorValues.ToList()
                              where it.CreateOn.Year == year
                              && it.DeleteOn.HasValue == false
                              && it.FactorId == 3
                              group it by it.CreateOn.Date.ToString("MM") into grouping
                              select new
                              {
                                  Key = int.Parse(grouping.Key),
                                  Value = grouping.Sum(it => it.Value) / grouping.Count(),
                              };
                var oilQry = from it in db.FactorValues.ToList()
                             where it.CreateOn.Year == year
                             && it.DeleteOn.HasValue == false
                             && it.FactorId == 4
                             group it by it.CreateOn.Date.ToString("MM") into grouping
                             select new
                             {
                                 Key = int.Parse(grouping.Key),
                                 Value = grouping.Sum(it => it.Value) / grouping.Count(),
                             };

                foreach (var item in bathQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.Money = item.Value;
                }
                foreach (var item in rateQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.Rate = item.Value;
                }
                foreach (var item in goldQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.Gold = item.Value;
                }
                foreach (var item in oilQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.Oil = item.Value;
                }

                #endregion คำนวณปัจจัย

                #region คำนวณราคาขาย

                var plasticQry = from tracking in db.ProductPricingPerUnitTrackings.ToList()
                                 where tracking.Product.CategoryId == 1
                                 && tracking.CreateOn.Year == year
                                 && tracking.DeleteOn.HasValue == false
                                 && tracking.Product.DeleteOn.HasValue == false
                                 group tracking by tracking.CreateOn.Date.ToString("MM") into grouping
                                 select new
                                 {
                                     Key = int.Parse(grouping.Key),
                                     Value = grouping.Sum(it => it.SellingPrice) / grouping.Count(),
                                 };

                var steelQry = from tracking in db.ProductPricingPerUnitTrackings.ToList()
                               where tracking.Product.CategoryId == 2
                               && tracking.CreateOn.Year == year
                               && tracking.DeleteOn.HasValue == false
                        && tracking.Product.DeleteOn.HasValue == false
                               group tracking by tracking.CreateOn.Date.ToString("MM") into grouping
                               select new
                               {
                                   Key = int.Parse(grouping.Key),
                                   Value = grouping.Sum(it => it.SellingPrice) / grouping.Count(),
                               };
                var paperQry = from tracking in db.ProductPricingPerUnitTrackings.ToList()
                               where tracking.Product.CategoryId == 3
                               && tracking.CreateOn.Year == year
                               && tracking.DeleteOn.HasValue == false
                        && tracking.Product.DeleteOn.HasValue == false
                               group tracking by tracking.CreateOn.Date.ToString("MM") into grouping
                               select new
                               {
                                   Key = int.Parse(grouping.Key),
                                   Value = grouping.Sum(it => it.SellingPrice) / grouping.Count(),
                               };

                foreach (var item in plasticQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.SellPricePlastic = item.Value;
                }
                foreach (var item in steelQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.SellPriceSteel = item.Value;
                }
                foreach (var item in paperQry)
                {
                    var month = result.FirstOrDefault(it => it.Month == item.Key);
                    month.SellPricePaper = item.Value;
                }

                #endregion คำนวณราคาขาย

                return result.ToList();
            }
        }


        // ----------------------------------------------------


        // รายวันเฉพาะรายการซื้อ
        public static IEnumerable<DisplayReport> GetReportDaily(int selectedDay, int selectedMonth, int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.ProductTrackings
                             where it.CreateOn.Day == selectedDay
                             && it.CreateOn.Month == selectedMonth
                             && it.CreateOn.Year == selectedYear
                             && it.DeleteOn.HasValue == false
                             && it.Action == "B"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.PurchasingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }

        //รายวันเฉพราะขาย
        public static IEnumerable<DisplayReport> GetSellReportDaily(int selectedDay, int selectedMonth, int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.ProductTrackings
                             where it.CreateOn.Day == selectedDay
                             && it.CreateOn.Month == selectedMonth
                             && it.CreateOn.Year == selectedYear
                             && it.DeleteOn.HasValue == false
                             && it.Action == "s"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.SellingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }



        // รายเดือนการซื้อ
        public static IEnumerable<DisplayReport> GetReportMonthly(int selectedMonth, int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.ProductTrackings
                             where it.CreateOn.Month == selectedMonth
                             && it.CreateOn.Year == selectedYear
                             && it.DeleteOn.HasValue == false
                             && it.Action == "B"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.PurchasingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }


        // รายเดือนการขาย
        public static IEnumerable<DisplayReport> GetSellReportMonthly(int selectedMonth, int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.ProductTrackings
                             where it.CreateOn.Month == selectedMonth
                             && it.CreateOn.Year == selectedYear
                             && it.DeleteOn.HasValue == false
                             && it.Action == "S"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.SellingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }



        // ตามช่วงเวลาเฉพราะซื้อ
        public static IEnumerable<DisplayReport> GetReportByRange(int fromDay, int fromMonth, int fromYear, int thruDay, int thruMonth, int thruYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var fromDate = new DateTime(fromYear, fromMonth, fromDay);
                var thruDate = new DateTime(thruYear, thruMonth, thruDay);
                var result = from it in db.ProductTrackings.ToList()
                             where (it.CreateOn.Date >= fromDate.Date)
                             && (it.CreateOn.Date <= thruDate.Date)
                             && it.DeleteOn.HasValue == false
                             && it.Action == "B"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.PurchasingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }

        // ตามช่วงเวลาเฉพราะขาย
        public static IEnumerable<DisplayReport> GetSellReportByRange(int fromDay, int fromMonth, int fromYear, int thruDay, int thruMonth, int thruYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var fromDate = new DateTime(fromYear, fromMonth, fromDay);
                var thruDate = new DateTime(thruYear, thruMonth, thruDay);
                var result = from it in db.ProductTrackings.ToList()
                             where (it.CreateOn.Date >= fromDate.Date)
                              && (it.CreateOn.Date <= thruDate.Date)
                             && it.DeleteOn.HasValue == false
                             && it.Action == "S"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.SellingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }
        // รายปีการซื้อ
        public static IEnumerable<DisplayReport> GetReportYearly(int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.ProductTrackings
                             where it.CreateOn.Year == selectedYear
                             && it.DeleteOn.HasValue == false
                             && it.Action == "B"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.PurchasingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }
        ////รายปีการขาย
        //public static IEnumerable<DisplayReport> GetSellYearly(int selectedYear)
        //{
        //    using (var db = new Shared.DAC.RBSFPPModelContainer())
        //    {
        //        var result = from it in db.ProductTrackings
        //                     where it.CreateOn.Year == selectedYear
        //                     && it.DeleteOn.HasValue == false
        //                     && it.Action == "S"
        //                     select new DisplayReport
        //                     {

        //                         MemberName = it.Member.FirstName,
        //                         CategoryName = it.Product.Category.Name,
        //                         ProductName = it.Product.Name,
        //                         Amount = it.Unit,
        //                         Price = it.ProductPricingPerUnit.SellingPrice,
        //                         OnDate = it.CreateOn,
        //                         Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
        //                     };

        //    }
        //}
        // รายปีการขาย
        public static IEnumerable<DisplayReport> GetSellReportYearly(int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.ProductTrackings
                             where it.CreateOn.Year == selectedYear
                             && it.DeleteOn.HasValue == false
                             && it.Action == "S"
                             select new DisplayReport
                             {
                                 MemberName = it.Member.FirstName,
                                 CategoryName = it.Product.Category.Name,
                                 ProductName = it.Product.Name,
                                 Amount = it.Unit,
                                 Price = it.Unit * it.ProductPricingPerUnit.SellingPrice,
                                 OnDate = it.CreateOn,
                                 Action = (it.Action == "B") ? "ซื้อ" : "ขาย"
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }


        //กราฟ
        public static IEnumerable<DisplayReport> GetGraph(int selectedYear)
        {
            using (var db = new Shared.DAC.RBSFPPModelContainer())
            {
                var result = from it in db.FactorValues
                             where it.CreateOn.Year == selectedYear && it.DeleteOn.HasValue == false
                             //group it by it.CreateOn.Date into newfactor

                             select new DisplayReport
                             {
                                 FactorName = it.Factor.FactorName,
                                 Values = it.Value,
                                 OnDate = it.CreateOn
                             };

                // Short hand of if
                // var resultx = (5 > 4) ? 9 : 3;

                return result.ToList();
            }
        }
    }
}
